Database Entities

RatingAppointment

Represents a customer rating for an appointment including ratings for provider, staff, clinic, and overall.

Column	Type	Description
id	number	Primary key, auto-generated identifier.
customer	Customer	Many-to-one relation to the customer who gave the rating.
bookingId	string	Unique booking identifier (varchar 128). This is emrAppointmentId for external appointments.
businessLocation	BusinessLocation	Many-to-one relation to the business location rated.
agent	Agent	Many-to-one relation to the agent rated.
overallRating	float	Overall rating score (default 0).
providerRating	float	Rating for the service provider (default 0).
staffRating	float	Rating for the staff (default 0).
clinicRating	float	Rating for the clinic (default 0).
comments	string	Optional comments provided by the customer (nullable).
tags	string	Optional tags related to the rating (nullable).
status	enum	Status of rating: pending, approved, or rejected.
datetimeRated	timestamp	Timestamp when the rating was created.
approvedBy	Agent	Agent who approved or rejected the rating (nullable).
datetimeApproved	timestamp	Timestamp when the rating was approved or rejected (nullable).

Unique Constraints

• bookingId must be unique across ratings.

Methods

• approve(agent: Agent): Marks rating as approved by the given agent with timestamp.
• reject(agent: Agent): Marks rating as rejected by the given agent with timestamp.

---

RatingClinic

Represents aggregate rating data for a business location's clinic.

Column	Type	Description
id	number	Primary key, auto-generated identifier.
businessLocation	BusinessLocation	Many-to-one relation to the business location.
overallRating	float	Sum of all overall ratings for the clinic.
overallCount	int	Number of ratings submitted.

Computed Property

• averageRating: Returns average rating as overallRating / overallCount or 0 if count is 0.

---

RatingProvider

Represents aggregate rating data for a service provider (agent) at a business location.

Column	Type	Description
id	number	Primary key, auto-generated identifier.
businessLocation	BusinessLocation	Many-to-one relation to business location.
agent	Agent	Many-to-one relation to the agent (provider).
overallRating	float	Sum of all overall ratings for the provider.
overallCount	int	Number of ratings submitted.
tags	string	Optional tags related to ratings (nullable).

Computed Property

• averageRating: Returns average rating as overallRating / overallCount or 0 if count is 0.

---

RatingStaff

Represents aggregate rating data for staff at a business location.

Column	Type	Description
id	number	Primary key, auto-generated identifier.
businessLocation	BusinessLocation	Many-to-one relation to business location.
overallRating	float	Sum of all overall ratings for the staff.
overallCount	int	Number of ratings submitted.
tags	string	Optional tags related to ratings (nullable).

Computed Property

• averageRating: Returns average rating as overallRating / overallCount or 0 if count is 0.

---

RatingNotification

Represents notifications sent regarding rating requests or reminders for appointments.

Column	Type	Description
id	number	Primary key, auto-generated identifier.
appointmentId	bigint	Appointment identifier linked to the notification.
isInternal	boolean	Flag indicating if appointment is internal or not.
type	NotificationType	Enum defining notification type (e.g. email, sms).
cycleType	NotificationCycleType	Enum defining notification cycle: initial, reminder1, reminder2.
status	NotificationStatus	Status of notification: OPEN(opn), RATED(rtd), CANCELLED(cxl).
customer	Customer	Many-to-one relation to the customer (nullable).
sentAt	timestamp	Timestamp when notification was sent (nullable).

Unique Constraints

• Unique combination of (appointmentId, type, cycleType) to avoid duplicate notifications.

---

Utility: averageRating

Helper function to calculate average rating given sum and count.

export const averageRating = (rating: number, count: number) => {
  return count > 0 ? rating / count : 0;
};

---